package com.rockcent.mall.lib.repository;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.rockcent.common.common.SQLListDoing;
import com.rockcent.common.common.SQLObjectDoing;
import com.rockcent.mall.lib.domain.ProductEvent;
import com.rockcent.mall.lib.dto.MerchantCustomerStatisticsDto;
import com.rockcent.mall.lib.dto.StatisticDto;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by DK on 16/9/19.
 * 统计
 */
@Repository
public class ProductEventRepositoryImpl implements ProductEventRepositoryCustom {

    private static final Logger log = LoggerFactory.getLogger(ProductEventRepositoryImpl.class);

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    @Autowired
    private ObjectMapper objectMapper;

    @Override
    public List<ProductEvent> findProductStatisticByproductId(Long productId) {
        SQLListDoing<ProductEvent> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select event_type, count(distinct p.buyer_id) as num from product_event p where p.product_id =:productId and p.buyer_id is not null group by event_type");
            params.put("productId", productId);
        };
        return doing.go(ProductEvent.class, jdbcTemplate, log, objectMapper);
    }

    public StatisticDto findCustomerShareInCome(Long sharerId) {
        SQLObjectDoing<StatisticDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT SUM(pe.share_fee) AS statisticSum FROM product_event pe WHERE pe.sharer_id =:sharerId and pe.event_type = \"ORDERED\" ");
            params.put("sharerId", sharerId);
        };
        return doing.go(StatisticDto.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public List<ProductEvent> getTop10(Long customerId, Long mallId) {
        SQLListDoing<ProductEvent> doing = (sql, fields, params, typeReferenceMap) -> {
            //统计该用户近30天访问量的前10的访问记录
            sql.append(" SELECT %s FROM product_event pe WHERE  pe.event_type = \"VIEWED\"  AND pe.date_created > DATE_SUB(NOW(), INTERVAL 30 DAY) and pe.mall_id = :mallId");
            if (customerId != null) {
                sql.append(" AND pe.buyer_id = :customerId ");
                params.put("customerId", customerId);
            }
            sql.append("GROUP BY pe.product_id ORDER BY COUNT(1) DESC LIMIT 0,10 ");
            params.put("mallId", mallId);
            fields.append("pe.*");
        };
        return doing.go(ProductEvent.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public List<ProductEvent> findBySharerIdAndDateCreatedBetween(Long id, String firstDate, String lastDate) {
        SQLListDoing<ProductEvent> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select pe.* from product_event pe where pe.sharer_id = :buyerId and (pe.date_created between :firstDate and :lastDate) ");
            params.put("buyerId", id);
            params.put("firstDate", firstDate);
            params.put("lastDate", lastDate);
        };
        return doing.go(ProductEvent.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 每日客户来源列表
     *
     * @param merchantId
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public List<MerchantCustomerStatisticsDto> findCustomersByMerchantIdandDate(Long mallId, Long merchantId, String startDate, String endDate, String groupBy) {
        SQLListDoing<MerchantCustomerStatisticsDto> doing = (sql, fields, params, typeReferenceMap) -> {
            String dateFormat = "DATE_FORMAT(pe.date_created,'%Y.%m.%d')";
            String groupName = "date(pe.date_created)";
            if ("hour".equals(groupBy)) {
                dateFormat = "date_format(pe.date_created,'%H')";
                groupName = "date_format(pe.date_created,'%H')";
            } else if ("day".equals(groupBy)) {
                dateFormat = "date_format(pe.date_created,'%Y.%m.%d')";
                groupName = "date(pe.date_created)";
            } else if ("month".equals(groupBy)) {
                dateFormat = "date_format(pe.date_created,'%Y.%m')";
                groupName = "date_format(pe.date_created,'%Y.%m')";
            }
            sql.append("select count(distinct pe.buyer_id) as sourceCustomerNum," + dateFormat + " as sourceCustomerDate,'wechat' as sourceType from product_event pe");
            sql.append(" where pe.merchant_id=:merchantId");
            sql.append(" and pe.mall_id=:mallId");
            sql.append(" and pe.buyer_id is not null");
            sql.append(" and pe.event_type in('CLICKED')");
            sql.append(" and pe.date_created>=:startDate");
            sql.append(" and pe.date_created<=:endDate");
            sql.append(" and pe.is_delete='NO'");
            sql.append(" group by " + groupName);
            params.put("merchantId", merchantId);
            params.put("mallId", mallId);
            params.put("startDate", startDate);
            params.put("endDate", endDate);
        };
        return doing.go(MerchantCustomerStatisticsDto.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public List<ProductEvent> findTwoHourData() {
        SQLListDoing<ProductEvent> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM product_event p WHERE p.date_created > DATE_SUB(NOW(), INTERVAL 120 MINUTE);");
            fields.append("p.*");
        };
        return doing.go(ProductEvent.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 产品类型的统计数
     *
     * @param productId
     * @param mallId
     * @param merchantId
     * @return
     */
    @Override
    public List<ProductEvent> findProductStatisticViewCountByproductId(Long productId, Long mallId, Long merchantId) {
        SQLListDoing<ProductEvent> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select event_type, count(1) as num from product_event p where p.product_id =:productId and p.buyer_id is not null and p.mall_id=:mallId and p.merchant_id=:merchantId group by event_type");
            params.put("productId", productId);
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
        };
        return doing.go(ProductEvent.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public List<Long> getTop10WithinThreeMonths(Long customerId, Long mallId) {
        SQLListDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            //统计该用户近30天访问量的前10的访问记录
            sql.append(" SELECT %s FROM product_event pe INNER JOIN product p ON pe.product_id = p.id WHERE  pe.event_type = \"VIEWED\"  AND pe.date_created > DATE_SUB(NOW(), INTERVAL 60 DAY) AND p.`status` ='SELLING' and pe.mall_id = :mallId");
            if (customerId != null) {
                sql.append(" AND pe.buyer_id = :customerId ");
                params.put("customerId", customerId);
            }
            sql.append("GROUP BY pe.product_id ORDER BY COUNT(1) DESC LIMIT 0,10 ");
            params.put("mallId", mallId);
            fields.append("pe.product_id");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public List<Long> findTop10ProductsOderbyTotalSaled(List<Long> productIdList) {
        SQLListDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from product_event ");

            sql.append(" where event_type='SALED'");
            if (productIdList != null && productIdList.size() > 0) {
                sql.append(" AND product_id IN(:productIds) ");
                params.put("productIds", productIdList);
            }
            sql.append(" group by product_id ");
            sql.append(" order by sum(num) desc ");
            sql.append(" limit 1000 ");

            fields.append("product_id");


        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }
}
